﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using SubSonic;
using ss = SubSonic;
using SubSonicResearch;

namespace OrmSmackdown.SubSonic
{
    public class Exerciser : IExerciser
    {
        #region Insert
        public void Insert()
        {
            using (SharedDbConnectionScope scope = new SharedDbConnectionScope())
            {
                var managerRole = new SubSonicResearch.Role { RoleName = Constants.ManagerRole, IsForPortal = true, IsForContact = false };
                var employeeRole = new SubSonicResearch.Role { RoleName = Constants.EmployeeRole, IsForPortal = true, IsForContact = true };
                var contactRole = new SubSonicResearch.Role { RoleName = Constants.ContactRole, IsForPortal = false, IsForContact = true };

                var company1 = new SubSonicResearch.Company { CompanyName = Constants.Company1 };
                var company2 = new SubSonicResearch.Company { CompanyName = Constants.Company2 };

                /* Note you must save the role first to get the RoleId */
                managerRole.Save();
                employeeRole.Save();
                contactRole.Save();

                company1.Save();
                company2.Save();

                var user1 = new SubSonicResearch.Person { FirstName = Constants.User1FirstName, LastName = Constants.User1LastName, Email = Constants.User1Email, Role = managerRole, UserType = "US" };
                var user2 = new SubSonicResearch.Person { FirstName = Constants.User2FirstName, LastName = Constants.User2LastName, Email = Constants.User2Email, Role = employeeRole, UserType = "US" };
                var user3 = new SubSonicResearch.Person { FirstName = Constants.User3FirstName, LastName = Constants.User3LastName, Email = Constants.User3Email, Role = managerRole, UserType = "US" };
                var user4 = new SubSonicResearch.Person { FirstName = Constants.User4FirstName, LastName = Constants.User4LastName, Email = Constants.User4Email, Role = employeeRole, UserType = "US" };
                var user5 = new SubSonicResearch.Person { FirstName = Constants.User5FirstName, LastName = Constants.User5LastName, Email = Constants.User5Email, Role = employeeRole, UserType = "US" };

                user1.Save();
                user2.Save();
                user3.Save();
                user4.Save();
                user5.Save();

                var portal1 = new SubSonicResearch.Portal { PortalName = Constants.Portal1, Person = user1 };
                var portal2 = new SubSonicResearch.Portal { PortalName = Constants.Portal2, Person = user2 };

                portal1.Save();
                portal2.Save();

                var portalPerson1 = new SubSonicResearch.PortalPeopleXref { Portal = portal1, Person = user1 };

                var portalPerson21 = new SubSonicResearch.PortalPeopleXref { Portal = portal1, Person = user2 };
                var portalPerson22 = new SubSonicResearch.PortalPeopleXref { Portal = portal2, Person = user2 };

                var portalPerson31 = new SubSonicResearch.PortalPeopleXref { Portal = portal1, Person = user3 };
                var portalPerson32 = new SubSonicResearch.PortalPeopleXref { Portal = portal2, Person = user3 };

                var portalPerson4 = new SubSonicResearch.PortalPeopleXref { Portal = portal1, Person = user4 };

                var portalPerson5 = new SubSonicResearch.PortalPeopleXref { Portal = portal2, Person = user5 };

                portalPerson1.Save();
                portalPerson21.Save();
                portalPerson22.Save();
                portalPerson31.Save();
                portalPerson32.Save();
                portalPerson4.Save();
                portalPerson5.Save();

                var contact1 = new SubSonicResearch.Person { FirstName = Constants.Contact1FirstName, LastName = Constants.Contact1LastName, Email = Constants.Contact1Email, Role = contactRole, Company = company1, UserType = "CN" };
                var contact2 = new SubSonicResearch.Person { FirstName = Constants.Contact2FirstName, LastName = Constants.Contact2LastName, Email = Constants.Contact2Email, Role = contactRole, Company = company1, UserType = "CN" };
                var contact3 = new SubSonicResearch.Person { FirstName = Constants.Contact3FirstName, LastName = Constants.Contact3LastName, Email = Constants.Contact3Email, Role = contactRole, Company = company2, UserType = "CN" };
                var contact4 = new SubSonicResearch.Person { FirstName = Constants.Contact4FirstName, LastName = Constants.Contact4LastName, Email = Constants.Contact4Email, Role = contactRole, Company = company2, UserType = "CN" };
                var contact5 = new SubSonicResearch.Person { FirstName = Constants.Contact5FirstName, LastName = Constants.Contact3LastName, Email = Constants.Contact5Email, Role = contactRole, Company = company2, UserType = "CN" };
                var contact6 = new SubSonicResearch.Person { FirstName = Constants.Contact6FirstName, LastName = Constants.Contact4LastName, Email = Constants.Contact6Email, Role = contactRole, Company = company2, UserType = "CN" };

                contact1.Save();
                contact2.Save();
                contact3.Save();
                contact4.Save();
                contact5.Save();
                contact6.Save();

                AddAddress(user1, "1000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(user2, "2000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(user3, "3000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(user4, "4000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(user5, "5000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(user1, "1100 Some St.", string.Empty, Constants.TownWeCareAbout, "ST", "12345", "1234", true);
                AddAddress(user2, "1200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(user3, "1300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(user4, "1400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(user5, "1500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);

                AddAddress(contact1, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(contact2, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(contact3, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(contact4, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(contact5, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(contact6, "7000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", false);
                AddAddress(contact1, "8000 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(contact2, "8100 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(contact3, "8200 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(contact4, "8300 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(contact5, "8400 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
                AddAddress(contact6, "8500 Some St.", string.Empty, Constants.SomeOtherTown, "ST", "12345", "1234", true);
            }
        }

        private void AddAddress(Person p, string line1, string line2, string city, string state, string zip, string zip4, bool isDefault)
        {
            var addr = new Address { Line1 = line1, Line2 = line2, City = city, State = state, ZipCode = zip, ZipCodePlusFour = zip4, Person = p, IsDefault = isDefault };

            addr.Save();
        }
        #endregion

        public IList<string> GetPortalAdminEmails()
        {
            var query = new Select(Person.Columns.Email)
            .From<Person>()
            .InnerJoin<Portal>();

            List<String> list = new List<String>();
            foreach (System.Data.DataRow row in query.ExecuteDataSet().Tables[0].Rows)
            {
                list.Add((String)row[0]);
            }
            return list;            
        }

        public IList<string> GetContactEmailsForCompany(string companyName)
        {
            var query = new Select(Person.Columns.Email)
            .From<Person>()
            .InnerJoin<Company>()
            .Where(Company.Columns.CompanyName).IsEqualTo(companyName);

            List<String> list = new List<String>();
            foreach (System.Data.DataRow row in query.ExecuteDataSet().Tables[0].Rows)
            {
                list.Add((String)row[0]);
            }

            return list;    
        }

        public int GetNumberOfPortals()
        {
            var query = new Select(ss.Aggregate.Count(Portal.Columns.PortalId))
                .From(Portal.Schema);

            return (int)query.ExecuteScalar();
        }

        public int GetNumberOfUsers()
        {
            var query = new Select(ss.Aggregate.Count(Person.Columns.PersonId))
                .From<Person>()
                .Where(Person.Columns.UserType).IsEqualTo("US");

            return (int)query.ExecuteScalar();
        }

        public int GetNumberOfContacts()
        {
            var query = new Select(ss.Aggregate.Count(Person.Columns.PersonId))
                .From<Person>()
                .Where(Person.Columns.UserType).IsEqualTo("CN");

            return (int)query.ExecuteScalar();
        }

        public string GetDefaultCity(string email)
        {
            var query = new Select(Address.Columns.City)
              .From<Address>()
              .InnerJoin<Person>()
              .Where(Address.Columns.IsDefault).IsEqualTo(true)
                .And(Person.Columns.Email).IsEqualTo(email);

            return query.ExecuteScalar() as string;
        }

        public string GetAdminForPortal(string portalName)
        {
            var query = new Select(Person.Columns.Email)
              .From(Person.Schema)
              .InnerJoin(Portal.Schema)
              .Where(Portal.Columns.PortalName).IsEqualTo(portalName);

            return query.ExecuteScalar() as string;
        }

        public double GetAverageUsersPerPortal()
        {
            var query = new Select(
                ss.Aggregate.GroupBy(Portal.Columns.PortalId),
                ss.Aggregate.Count(Portal.Columns.PortalId))
                        .From<PortalPeopleXref>();

            int sum = 0;
            int portalCount = 0;
            using (var rdr = query.ExecuteReader())
            {
                while (rdr.Read())
                {
                    sum += (int)rdr[1];
                    portalCount++;
                }
            }

            if (portalCount > 0)
                return (double)sum / (double)portalCount;
            else
                return 0.0;
        }

        public IList<string> GetUserEmails()
        {
            var query = new Select(Person.Columns.Email)
                         .From(Person.Schema)
                         .Where(Person.Columns.UserType).IsEqualTo("US");

            using (var rdr = query.ExecuteReader())
            {
                var emails = new List<string>();
                while (rdr.Read())
                    emails.Add(rdr[Person.Columns.Email].ToString());

                return emails;
            }
        }

        public IList<string> GetContactEmails()
        {
            //IList<string> emails;
            //using (SharedDbConnectionScope scope = new SharedDbConnectionScope())
            //{
                var query = new Select(Person.Columns.Email)
                             .From(Person.Schema)
                             .Where(Person.Columns.UserType).IsEqualTo("CN");

                List<String> list = new List<String>();
                foreach (System.Data.DataRow row in query.ExecuteDataSet().Tables[0].Rows)
                {
                    list.Add((String)row[0]);
                }
                return list;            

            //    return emails;
            //}
        }

        public void Dispose()
        {

        }
    }

    public static class SubSonicSqlQueryExtensionMethods
    {
        public static List<String> ExecuteTypedList(this SqlQuery qry)
        {
            List<String> list = new List<String>();
            foreach (System.Data.DataRow row in qry.ExecuteDataSet().Tables[0].Rows)
            {
                list.Add((String)row[0]);
            }
            return list;
        }
    }
}
